﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Interfaces.CommonData;

namespace StockInfoDataLayer
{
    public class PriceTable
    {

        public void ClearTable()
        {
            DBManager mgr = DBManager.GetInstance();
            using (var conn = mgr.CreateConnection())
            {
                conn.Open();
                SqlCommand cmdDelete = new SqlCommand();
                cmdDelete.CommandText = "DELETE FROM PriceTable";
                cmdDelete.Connection = conn;
                cmdDelete.ExecuteNonQuery();
                conn.Close();
            }
        }

        // Populate full list first than insert into database
        public void Populate(List<StockPrice> prices)
        {
            DBManager mgr = DBManager.GetInstance();
            string sqlInsert = "INSERT INTO PriceTable VALUES({0},'{1}',{2})";
            using (var conn = mgr.CreateConnection())
            {
                conn.Open();

                SqlCommand cmdInsert = new SqlCommand();
                cmdInsert.Connection = conn;

                prices.ForEach(price =>
                {
                    cmdInsert.CommandText = string.Format(sqlInsert, price.StockId, price.Date, price.AdjPrice);
                    cmdInsert.ExecuteNonQuery();
                });
                conn.Close();
            }
        }

        public void PopulateBulk(List<StockPrice> prices)
        {
            DBManager mgr = DBManager.GetInstance();
            DataTable priceTableData = new DataTable("PriceTable");

            DataColumn stockIdColumn = new DataColumn("StockID", Type.GetType("System.Int32"));
            DataColumn dateColumn = new DataColumn("Date", Type.GetType("System.DateTime"));
            DataColumn priceColumn = new DataColumn("Adjusted Closing Price", Type.GetType("System.Decimal"));
            
            priceTableData.Columns.Add(stockIdColumn);
            priceTableData.Columns.Add(dateColumn);
            priceTableData.Columns.Add(priceColumn);
            
            prices.ForEach(pr =>
            {
                DataRow rw = priceTableData.NewRow();
                rw[stockIdColumn.ColumnName] = pr.StockId;
                rw[dateColumn.ColumnName] = pr.Date;
                rw[priceColumn.ColumnName] = pr.AdjPrice;
                priceTableData.Rows.Add(rw);
            });


            // Copy the DataTable to SQL Server using SqlBulkCopy
            using (var dbConnection = mgr.CreateConnection())
            {
                dbConnection.Open();
                using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
                {
                    s.DestinationTableName = priceTableData.TableName;

                    foreach (var column in priceTableData.Columns)
                        s.ColumnMappings.Add(column.ToString(), column.ToString());

                    s.WriteToServer(priceTableData);
                }
            }
        }


        public DateInterval RetrieveMaxDateInterval()
        {
            DBManager mgr = DBManager.GetInstance();
            string sqlSelect = "SELECT Min(Date) mx, Max(Date) mn FROM PriceTable";
            DateInterval res = new DateInterval();
            
            using (var conn = mgr.CreateConnection())
            {
                conn.Open();
                SqlCommand cmdSelect = new SqlCommand();
                cmdSelect.CommandText = sqlSelect;
                cmdSelect.Connection = conn;
                try
                {
                    SqlDataReader rd = cmdSelect.ExecuteReader();
                    while (rd.Read())
                    {
                        res.DateFrom = (DateTime)rd[0];
                        res.DateTo = (DateTime)rd[1];
                    }
                    rd.Close();
                }
                finally
                {
                    conn.Close();
                }
            }
            return res;
        }

        public List<StockPrice> GetAllPrices()
        {
            DBManager mgr = DBManager.GetInstance();

            List<StockPrice> allPrices = new List<StockPrice>();
            string sqlSelect = "SELECT StockID, Date, [Adjusted Closing Price] FROM PriceTable ";

            using (var conn = mgr.CreateConnection())
            {
                conn.Open();
                SqlCommand cmdSelect = new SqlCommand();
                cmdSelect.CommandText = sqlSelect;
                cmdSelect.Connection = conn;
                try
                {
                    SqlDataReader rd = cmdSelect.ExecuteReader();
                    while (rd.Read())
                    {
                        StockPrice p = new StockPrice() { StockId = (int)rd[0], Date = (DateTime)rd[1], AdjPrice = (Decimal)rd[2] };
                        allPrices.Add(p);
                    }
                    rd.Close();

                }
                finally { conn.Close(); }

                
            }

            return allPrices;

        }

    }
}
